-- STANDARD SCRIPT
-- STEP 1 BELOW HAS TO BE CUSTOMIZED FOR EACH CLIENT 
--


--------------------------------------------------------------------------------------
-- FUNCTION TO UPDATE BATCH TRANS TABLE



\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION upd_batch_trans_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_trans_t limit 1;
SELECT INTO v_cust_id cust_id from batch_trans_t limit 1;

PERFORM ml_upd_batch_status_trans_new_f(v_cust_id,v_batch_run_date,1,3,'batch_trans_t UPDATE');


-- STEP 1
-- AFTER THIS STEP, PAYMENTS SHOULD BE IDENTIFIED WITH THE LETTER P IN trans_type.
-- ALSO ALL PAYMENTS SHOULD BE CLASSIFIED IN trans_type_detail.  NULL VALUES ARE OK (NOT MATCHED TO trans_cdm_desc_t)


RAISE NOTICE 'START UPDATE OF trans_type_detail IN batch_trans_t TABLE ';

UPDATE batch_trans_t
SET

 trans_type_detail = (CASE WHEN  batch_trans_t.cust_id = 49 AND batch_trans_t.payor_plan in ('950001','950002') THEN 'C'
                           ELSE                                                                                     a.medlytix_class 
                           END)

FROM
     trans_cdm_desc_t as a
WHERE
    a.cust_id = batch_trans_t.cust_id AND a.cdm = batch_trans_t.cdm

;

GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH UPDATE OF trans_type_detail IN batch_trans_t TABLE : %', v_status;



----------------------------------------------------
-- STEP 2
-- AFTER THIS STEP, medlytix_recon_flag SHOULD BE SET TO EITHER Y, P or N
-- ALSO trans_amt SHOULD BE SET SO THAT REGULAR TRANSACTIONS ARE POSTED WITH A +value


RAISE NOTICE 'START UPDATE OF medlytix_recon_flag IN batch_trans_t TABLE ';

UPDATE batch_trans_t
SET

 trans_amt = (CASE WHEN cust_id = 49 THEN -trans_amt
                   ELSE                    trans_amt
                   END),
 medlytix_recon_flag = (CASE WHEN cust_id = 49 AND 
                                  (trans_type_detail in ('M','C')) AND 
                                  (trans_amt IS NOT NULL and trans_amt <> 0) AND
                                  trans_date IS NOT NULL AND
                                  bill_date IS NOT NULL AND
                                  trans_date >= first_delivery_date AND 
                                  bill_date >= first_delivery_date                                                    THEN 'Y'
                             WHEN cust_id = 49 AND 
                                  trans_type_detail = 'D' AND 
                                  (trans_amt IS NOT NULL and trans_amt <> 0) AND
                                  trans_date IS NOT NULL AND
                                  bill_date IS NOT NULL AND
                                  trans_date >= first_delivery_date AND
                                  bill_date >= first_delivery_date AND
                                  (
                                    (payor_plan in ('550UIP','550UOP','550F12','550UHC','550F23','550FL5') AND medlytix_ins_type = 'D') 
                                    OR
                                     payor_plan in ('550MOL','550F35','550AMI','550FL8','550COV')
                                  )                                                                                   THEN 'Y'
                             WHEN cust_id = 49  AND
                                  trans_type = 'P' AND
                                  (trans_amt IS NOT NULL and trans_amt <> 0) AND
                                  (trans_type_detail IS NULL 
                                   OR
                                     (
                                      trans_type_detail IS NOT NULL AND
                                      trans_type_detail <> 'BD'   AND
                                      trans_type_detail <> 'W'    AND
                                      trans_type_detail <> 'P'    AND
                                      trans_type_detail <> 'BD'   AND
                                      trans_type_detail <> 'AUTO'
                                     ) 
                                  )                      AND
                                  trans_date IS NOT NULL AND
                                  bill_date IS NOT NULL  AND
                                  trans_date >= first_delivery_date AND
                                  bill_date >= first_delivery_date                                                    THEN 'P' 
                             WHEN cust_id = 49                                                                        THEN 'N'
                             ELSE                                                                                          NULL
                             END)
                                   
                               
;

GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'FINISH UPDATE OF medlytix_recon_flag IN batch_trans_t TABLE ';




UPDATE batch_status_trans_t
SET
   batch_trans_updated       = v_status
 WHERE
  (batch_status_trans_t.last_run_date = v_batch_run_date and batch_status_trans_t.cust_id = v_cust_id);



PERFORM ml_upd_batch_status_trans_new_f(v_cust_id,v_batch_run_date,1,3,'FINISH UPDATE OF batch_trans_t');


  return v_status;
END;
$$ LANGUAGE plpgsql;
